package fy.uap.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.LogFactory;

import com.seeyon.ctp.util.UUIDLong;
import fy.config.FyConfig;
import fy.uap.bean.InvalidProviderBean;
import fy.uap.bean.InvalidProviderFormBean;
import fy.uap.bean.RangeBean;
import fy.util.DBUtil;
import fy.util.MyUtil;

public class SrmInvalidProviderFormDao {
	private static org.apache.commons.logging.Log log = LogFactory.getLog(SrmInvalidProviderFormDao.class);

	private InvalidProviderFormBean invaliProviderFormBean;

	// private final static String findFy_supplier_vendor =
	// "select pk_vendor from fy_supplier_vendor where cvencode = ?";

	private final static String insertLog = "insert into fy_supplier_vendor_history(pk_venhis, pk_vendor, pk_corp, pk_invclass, changetype, changememo, changevouch, changecode, changer, ts, changedate, creater) select ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? from dual where exists( select * from fy_supplier_vendor_invclass where pk_vendor=? and trim(pk_corp)=? and pk_invclass=? and (cvendorclass='1' or cvendorclass='2') and (dr='0' or dr is null) )";

	private final static String uapUserID = "0001AA10000000009KX7";

	private Map<String, InvalidProviderBean> invalidProviderMap = new Hashtable<String, InvalidProviderBean>();

	private List<RangeBean> successRangeList;

	private static final String ERPUADATE = "update apps.FYG_PO_UAP_VENDORS@" + FyConfig.getERP_DB_CONFIG().getDblink()
			+ " av set (av.vendors_name, av.flag,av.zt,av.disable_date) = (select v.cvenname cvenname,      decode(sign(to_date(vi.denddate, 'YYYY-MM-DD') - sysdate),   -1,   'N',   decode(vi.cvendorclass, '3', 'N', 'Y')) flag   ,decode(sign(to_date(vi.denddate, 'YYYY-MM-DD') - sysdate),-1,'停用',   decode(vi.cvendorclass,          '1',          '合格',          '2',          '潜在',          '3',          '黑名单',          vi.cvendorclass)) zt,      to_date(vi.denddate, 'YYYY-MM-DD') disable_date from fy_supplier_vendor_invclass vi left join fy_supplier_vendor v on vi.pk_vendor = v.pk_vendor left join bd_corp c on vi.pk_corp = c.pk_corp left join fy_supplier_invclass i on vi.pk_invclass =       i.pk_invclass          where v.cvencode = ? and (vi.dr is null or vi.dr = 0) and av.vendors_num =v.cvencode and av.syb=c.unitname and av.uaplb = i.cinvclasscode )  where av.vendors_num =  ?";

	private List<RangeBean> failPRangeList;
	private List<RangeBean> failQRangeList;

	private long pChangeTrace;
	private long qChangeTrace;

	private List<RangeBean> rangeList;

	private boolean writeToErpSuccess = false;

	public boolean isWriteToErpSuccess() {
		return writeToErpSuccess;
	}

	public List<RangeBean> getFailPRangeList() {
		return failPRangeList;
	}

	public List<RangeBean> getFailQRangeList() {
		return failQRangeList;
	}

	public void setInvaliProviderFormBean(InvalidProviderFormBean invaliProviderFormBean) {
		this.invaliProviderFormBean = invaliProviderFormBean;
	}

	public boolean save() {

		boolean success = false;
		Connection conn = null;
		PreparedStatement pst = null;
		PreparedStatement pst1 = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());
			pChangeTrace = UUIDLong.longUUID();
			qChangeTrace = UUIDLong.longUUID();
			log.info("pChangeTrace:" + pChangeTrace + '\n' + "qChangeTrace:" + qChangeTrace);
			System.out.println("pChangeTrace:" + pChangeTrace + '\n' + "qChangeTrace:" + qChangeTrace);
			// 查詢pk_vendor cvencode
			StringBuilder sb = new StringBuilder();
			sb.append(
					"select pk_vendor, cvencode from fy_supplier_vendor where (dr='0' or dr is null) and (denddate is null or to_date(denddate,'yyyy-mm-dd') > to_date( to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd' )) and ( ");
			for (int i = 0, size = invaliProviderFormBean.getInvalidProviderList().size(); i < size; i++) {
				if (i != 0) {
					sb.append(" or ");
				}
				sb.append("cvencode =? ");
				
				InvalidProviderBean invaliProvider = invaliProviderFormBean.getInvalidProviderList().get(i);
				invalidProviderMap.put(invaliProvider.getProviderCode(), invaliProvider);
				
			}
			sb.append(")");
			pst = conn.prepareStatement(sb.toString());
			for (int i = 0, size = invaliProviderFormBean.getInvalidProviderList().size(); i < size; i++) {
				String code = invaliProviderFormBean.getInvalidProviderList().get(i).getProviderCode();
				pst.setString(i + 1, code);
			}
			rs = pst.executeQuery();
			while (rs.next()) {
				String pk_vendor = rs.getString(1);
				String code = rs.getString(2);
				invalidProviderMap.get(code).setPk_vendor(pk_vendor);
				log.info("查詢pk_vendor:" + pk_vendor + '\n' + "code:" + code);
			}
			DBUtil.close(rs, pst);
			conn.setAutoCommit(false);

			log.info("确定停用or黑名单getOpinion:" + invaliProviderFormBean.getOpinion());

			if ("1".equals(invaliProviderFormBean.getOpinion())) {
				// 停用 潜在cvendorclass=2 合格cvendorclass=1
				pst = conn.prepareStatement(
						"update fy_supplier_vendor_invclass set denddate=?, changeTrace=? where pk_vendor=? and trim(pk_corp)=? and pk_invclass=? and (dr='0' or dr is null) ");

				if (invaliProviderFormBean.getCompanyList() != null) {
					for (int o = 0, cSize = invaliProviderFormBean.getCompanyList().size(); o < cSize; o++) {
						String company = invaliProviderFormBean.getCompanyList().get(o);
						log.info("第" + (o + 1) + "个公司company:" + company);
						for (int i = 0, size = invaliProviderFormBean.getInvalidProviderList().size(); i < size; i++) {
							InvalidProviderBean invaliProvider = invaliProviderFormBean.getInvalidProviderList().get(i);
							// log.info("第"+(i+1)+"个供应商invaliProvider:"+ invaliProvider);
							// 潜在类别停用 
							log.info("getpTypeList():"+invaliProviderFormBean.getpTypeList()+"getqTypeList():"+invaliProviderFormBean.getqTypeList());
							if (invaliProviderFormBean.getpTypeList() != null) {
								for (int m = 0, tSize = invaliProviderFormBean.getpTypeList().size(); m < tSize; m++) {
									String pType = invaliProviderFormBean.getpTypeList().get(m);
									
									log.info("停用潜在pType:" + pType + "供应商invaliProvider:" + invaliProvider);
									
									disableType(pst, pst1, invaliProvider, pType, "2", company, pChangeTrace);
								}
							}
							// 合格类别停用
							if (invaliProviderFormBean.getqTypeList() != null) {
								for (int m = 0, tSize = invaliProviderFormBean.getqTypeList().size(); m < tSize; m++) {
									String qType = invaliProviderFormBean.getqTypeList().get(m);
									log.info("停用合格qType:" + qType + "供应商invaliProvider:" + invaliProvider);
									disableType(pst, pst1, invaliProvider, qType, "1", company, qChangeTrace);
								}
							}
						}
					}
				}

			} else {
				// 黑名单 cvendorclass=3
				pst = conn.prepareStatement(
						"update fy_supplier_vendor_invclass set cvendorclass=?, denddate=?, changeTrace=? where pk_vendor=? and trim(pk_corp)=? and ? is null and (dr='0' or dr is null)");
				DBUtil.close(pst1);
				pst1 = conn.prepareStatement(
						"insert into fy_supplier_vendor_history(pk_venhis, pk_vendor, pk_corp, pk_invclass, changetype, changememo, changevouch, changecode, changer, ts, changedate, creater) select ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? from dual where exists( select * from fy_supplier_vendor_invclass where pk_vendor=? and trim(pk_corp)=? and ? is null and (cvendorclass='1' or cvendorclass='2') and (dr='0' or dr is null) )");
				if (invaliProviderFormBean.getCompanyList() != null) {
					for (int o = 0, cSize = invaliProviderFormBean.getCompanyList().size(); o < cSize; o++) {
						String company = invaliProviderFormBean.getCompanyList().get(o);
						for (int i = 0, size = invaliProviderFormBean.getInvalidProviderList().size(); i < size; i++) {
							InvalidProviderBean invaliProvider = invaliProviderFormBean.getInvalidProviderList().get(i);
							log.info("黑名单:company:" + company + '\n' + "invaliProvider:" + invaliProvider + '\n'
									+ "Pk_vendor:" + invaliProvider.getPk_vendor());
							// 写入日志
						//	writeLog(pst1, invaliProvider, "", "", "黑名单", company);
							
							pst.setString(1, "3");
							pst.setString(2, "");
							pst.setLong(3, pChangeTrace);
							pst.setString(4, invaliProvider.getPk_vendor());
							pst.setString(5, company);
							pst.setString(6, "");
							// pst.setString(7, oldCvendorclass);
							pst.addBatch();
						}
					}
				}
			}
		//	pst1.executeBatch();
		//	DBUtil.close(pst1);
			pst.executeBatch();
			DBUtil.close(rs, pst);

			if ("1".equals(invaliProviderFormBean.getOpinion())) {
				pst = conn.prepareStatement(
						"select i.pk_corp, i.pk_invclass,i.changeTrace, i.pk_vendor from fy_supplier_vendor_invclass i where i.changeTrace=? or i.changeTrace=?");
				pst.setLong(1, qChangeTrace);
				pst.setLong(2, pChangeTrace);
				rs = pst.executeQuery();
				while (rs.next()) {
					if (successRangeList == null) {
						successRangeList = new ArrayList<RangeBean>();
					}
					
					log.info("88"+rs.getString(1)+rs.getString(2)+rs.getLong(3)+rs.getString(4));
					successRangeList
							.add(new RangeBean(rs.getString(1), rs.getString(2), rs.getLong(3), rs.getString(4)));
				}
			}
			
			conn.commit();
			success = true;
			sb = null;
			// 查询目标供应商编码对应的供应商信息
			sb = new StringBuilder().append(
					"select i.pk_corp, i.pk_invclass, c.unitname, si.cinvclasscode, v.cvencode, i.cvendorclass, v.cvenname, i.denddate from fy_supplier_vendor_invclass i left join bd_corp c on i.pk_corp = c.pk_corp left join fy_supplier_invclass si on si.pk_invclass = i.pk_invclass left join fy_supplier_vendor v on v.pk_vendor = i.pk_vendor where ");
			boolean first = true;
			if (invaliProviderFormBean.getInvalidProviderList() != null) {
				for (int i = 0, length = invaliProviderFormBean.getInvalidProviderList().size(); i < length; i++) {
					if (first) {
						sb.append(" v.cvencode in ( ?  ");
						first = false;
					} else {
						sb.append(" , ? ");
					}
				}
			}
			sb.append(
					") and (i.dr='0' or i.dr is null) and (i.denddate is null or to_date(i.denddate,'yyyy-mm-dd') > to_date( to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd' ))");
			
			
			pst = conn.prepareStatement(sb.toString());
			int index = 1;
			if (invaliProviderFormBean.getInvalidProviderList() != null) {
				for (int i = 0, length = invaliProviderFormBean.getInvalidProviderList().size(); i < length; i++) {
					pst.setString(index, invaliProviderFormBean.getInvalidProviderList().get(i).getProviderCode());
					index++;
				}
			}
			rs = pst.executeQuery();
			while (rs.next()) {
				// select i.pk_corp, i.pk_invclass, c.unitname,
				// si.cinvclasscode, v.cvencode, i.cvendorclass
				if (rangeList == null) {
					rangeList = new ArrayList<RangeBean>();
				}
				
				log.info("77"+rs.getString(1)+rs.getString(2)+rs.getString(3)+rs.getString(4)+rs.getString(5)+rs.getString(6)+rs.getString(7)+rs.getString(8));
				
				rangeList.add(new RangeBean(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(5),
						rs.getString(4), rs.getString(6), rs.getString(7), rs.getString(8)));
			}
			
			
			try {
				// ERP更新
				pst = conn.prepareStatement(ERPUADATE);
				for (int i = 0, size = invaliProviderFormBean.getInvalidProviderList().size(); i < size; i++) {
					pst.setString(1, invaliProviderFormBean.getInvalidProviderList().get(i).getProviderCode());
					pst.setString(2, invaliProviderFormBean.getInvalidProviderList().get(i).getProviderCode());
					pst.addBatch();
				}
				log.info("66");
				pst.executeBatch();
				conn.commit();
				writeToErpSuccess = true;
			} catch (Exception e) {
				DBUtil.rollback(conn);
				e.printStackTrace();
				log.error("《供应商导出》写入ERP数据库异常,单据编号： " + invaliProviderFormBean.getNumber(), e);
				writeToErpSuccess = false;
			}
		} catch (Exception e) {
			success = false;
			DBUtil.rollback(conn);
			e.printStackTrace();
			log.error("供应商导出审批数据导入UPA异常", e);
		} finally {
			DBUtil.setAutoCommit(conn, true);
			DBUtil.close(pst1);
			DBUtil.close(rs, pst, conn);
		}
	
/*		if ("1".equals(invaliProviderFormBean.getOpinion())) {
			setFailRangeList();
		}
		*/
		return success;
	}

	private void setFailRangeList() {
		log.info("FailRangeList 执行");
		List<String> companyList = invaliProviderFormBean.getCompanyList();
		int companySize = companyList == null ? 0 : companyList.size();
		int invalidProviderSize = invaliProviderFormBean.getInvalidProviderList().size();
		int pListSize = invaliProviderFormBean.getpTypeList() == null ? 0
				: invaliProviderFormBean.getpTypeList().size();
		int qListSize = invaliProviderFormBean.getqTypeList() == null ? 0
				: invaliProviderFormBean.getqTypeList().size();
		if (successRangeList == null || successRangeList.size() < (companySize * invalidProviderSize * pListSize
				+ companySize * invalidProviderSize * qListSize)) {
			boolean fail = true;
			for (int a = 0; a < invalidProviderSize; a++) {
				String pk_vendor = invaliProviderFormBean.getInvalidProviderList().get(a).getPk_vendor();
				for (int o = 0; o < companySize; o++) {
					String company = invaliProviderFormBean.getCompanyList().get(o);

					// 潜在
					for (int m = 0; m < pListSize; m++) {
						String pType = invaliProviderFormBean.getpTypeList().get(m);
						RangeBean bean = null;
						for (int i = 0, size = successRangeList == null ? 0 : successRangeList.size(); i < size; i++) {
							bean = successRangeList.get(i);
							if (bean.getChangeTrace() == pChangeTrace) {
								if (bean.getPk_corp().equals(company) && bean.getPk_invclass().equals(pType)
										&& pk_vendor.equals(bean.getPk_vendor())) {
									fail = false;
									break;
								}
							}
							bean = null;
						}
						if (fail) {
							if (failPRangeList == null) {
								failPRangeList = new ArrayList<RangeBean>();
							}
							bean = new RangeBean(company, pType, 0,
									invaliProviderFormBean.getInvalidProviderList().get(a).getProviderCode());
							failPRangeList.add(bean);
						}
						fail = true;
						bean = null;
					}
					// 合格
					for (int m = 0; m < qListSize; m++) {
						String qType = invaliProviderFormBean.getqTypeList().get(m);
						RangeBean bean = null;
						for (int i = 0, size = successRangeList == null ? 0 : successRangeList.size(); i < size; i++) {
							bean = successRangeList.get(i);
							if (bean.getChangeTrace() == qChangeTrace) {
								if (bean.getPk_corp().equals(company) && bean.getPk_invclass().equals(qType)
										&& pk_vendor.equals(bean.getPk_vendor())) {
									fail = false;
									break;
								}
							}
							bean = null;
						}
						if (fail) {
							if (failQRangeList == null) {
								failQRangeList = new ArrayList<RangeBean>();
							}
							bean = new RangeBean(company, qType, 0,
									invaliProviderFormBean.getInvalidProviderList().get(a).getProviderCode());
							failQRangeList.add(bean);
							bean = null;
						}
						log.info("FailRangeList 执行");
						fail = true;
					}
				}
			}
		}
	}

	/**
	 * 停用表单
	 * 
	 * @param pst
	 * @param pst1
	 * @param invaliProvider
	 * @param type
	 * @param oldCvendorclass
	 * @throws SQLException
	 */
	private void disableType(PreparedStatement pst, PreparedStatement pst1, InvalidProviderBean invaliProvider,
			String type, String oldCvendorclass, String company, long changeTrace) throws SQLException {
		if (type != null && !"".equals(type)) {
			// 写入日志
	//		writeLog(pst1, invaliProvider, type, oldCvendorclass, "停用", company);
			// update fy_supplier_vendor_invclass set denddate=?
			// where pk_vendor=? and trim(pk_corp)=? and pk_invclass=?
			// and cvendorclass=? and (dr='0' or dr is null)
			
			log.info("99999"+invaliProvider.getInvalidDate()+ invaliProvider.getPk_vendor()+company+type);
			pst.setString(1, invaliProvider.getInvalidDate().trim());
			pst.setLong(2, changeTrace);
			pst.setString(3, invaliProvider.getPk_vendor());
			pst.setString(4, company);
			pst.setString(5, type);
			// pst.setString(6, oldCvendorclass);
			// pst.setString(6, MyUtil.getTodayDateStr());
			pst.addBatch();
		}
	}

	/**
	 * 加入黑名单
	 * 
	 * @param pst
	 * @param pst1
	 * @param invaliProvider
	 * @param type
	 * @param oldCvendorclass
	 * @throws SQLException
	 */
	// private void writeToblackList(PreparedStatement pst,
	// PreparedStatement pst1, InvalidProviderBean invaliProvider,
	// String type, String oldCvendorclass, String company,
	// long changeTrace) throws SQLException {
	// if (type != null && !"".equals(type)) {
	//
	// writeLog(pst1, invaliProvider, type, oldCvendorclass, "黑名单",
	// company);
	//
	// pst.setString(1, "3");
	// pst.setString(2, "");
	// pst.setLong(3, changeTrace);
	// pst.setString(4, invaliProvider.getPk_vendor());
	// pst.setString(5, company);
	//
	// pst.setString(6, type);
	// // pst.setString(7, oldCvendorclass);
	// pst.addBatch();
	// }
	// }

	/**
	 * 写日志
	 * 
	 * @param pst1
	 * @param invaliProvider
	 * @param type
	 * @param oldCvendorclass
	 * @param operate
	 * @throws SQLException
	 */
	private void writeLog(PreparedStatement pst1, InvalidProviderBean invaliProvider, String type,
			String oldCvendorclass, String operate, String company) throws SQLException {
		// "insert into fy_supplier_vendor_history
		// (pk_venhis, pk_vendor, pk_corp, pk_invclass,
		// changetype, changememo, changevouch, changecode,
		// changer, ts) select ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
		// from dual where exists( select * from fy_supplier_vendor_invclass
		// where pk_vendor=? and trim(pk_corp)=? and pk_invclass=? and
		// cvendorclass=? )";
		 log.info("uuid:"+String.valueOf(UUIDLong.longUUID()));
		String uuid=String.valueOf(UUIDLong.longUUID());
         log.info("uuid:"+String.valueOf(uuid));
         log.info(uuid+","+invaliProvider.getPk_vendor()+","+company+","+type+","+operate+","+this.invaliProviderFormBean.getReason()+","+this.invaliProviderFormBean.getNumber()+","+uapUserID+","+ MyUtil.getNowStr()+","+ MyUtil.getTodayDateStr()+","+ invaliProviderFormBean.getMemberCode()+","+invaliProvider.getPk_vendor()+","+company+","+type);
		pst1.setString(1, uuid);
		pst1.setString(2, invaliProvider.getPk_vendor());
		pst1.setString(3, company);
		pst1.setString(4, type);
		pst1.setString(5, operate);
		pst1.setString(6, this.invaliProviderFormBean.getReason());
		pst1.setString(7, "SRMOA供应商导出批准表");
		pst1.setString(8, this.invaliProviderFormBean.getNumber());
		pst1.setString(9, uapUserID);
		pst1.setString(10, MyUtil.getNowStr());
		pst1.setString(11, MyUtil.getTodayDateStr());
		pst1.setString(12, invaliProviderFormBean.getMemberCode());
		pst1.setString(13, invaliProvider.getPk_vendor());
		pst1.setString(14, company);
		pst1.setString(15, type);
		pst1.addBatch();
	}
	
	
	
}
